﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

using System.Threading;
using System.Configuration;

namespace ABC.Trips
{
    public class Trips
    {
        public static Google.MapMarkerCollection<Google.DealerPickUpRequest> getDealerPickUpRequests(DateTime startDate,DateTime endDate,String tech=null)
        {
            Google.MapMarkerCollection<Google.DealerPickUpRequest> requests = new Google.MapMarkerCollection<Google.DealerPickUpRequest>();
            DateTime endTime = new DateTime(endDate.Year, endDate.Month, endDate.Day, 12, 59, 59);
            string newEndDate = String.Format("{0:MM/dd/yyyy h:m:s}", endTime);

            string sqlString = @"SELECT tabTRIP.TRIPDATE, tabTRIP.JOBSIZE, tabTRIP.TECH, tabADDRESS.LATITUDE, 
tabADDRESS.LONGITUDE, tabADDRESS.CODE AS CODE, tabTRIP.DEALERCODE, tabADDRESS.ADDR, tabZIP.CITY, tabZIP.STATE, 
tabZIP.ZIPCODE, tabTRIP.NOTE
FROM tabZIP INNER JOIN ((tabTRIP INNER JOIN tabDEALERS ON tabTRIP.DEALERCODE = tabDEALERS.CODE) INNER JOIN tabADDRESS 
ON tabDEALERS.ADDCODE = tabADDRESS.CODE) ON tabZIP.ZIPCODE = tabADDRESS.ZIP
WHERE (((tabTRIP.INVOICE)=0) AND ((tabTRIP.TRIPDATE) Between #" + String.Format("{0:MM/dd/yyyy}", startDate) + "# And #" + newEndDate + "#)";

           //  ( (tabTRIP.TRIPDATE) Between #" + String.Format("{0:MM/dd/yyyy}", startDate) + "# And #" + newEndDate + "#) ";
            if (tech != "*")
            {
                sqlString += "AND ((tabTRIP.TECH)='" + tech + "'))";
            }
            else
            {
                sqlString += ")";
            }
            OleDbCommand command = new OleDbCommand(sqlString, ABC.Database.DataBaseConnection.MyConn);
            OleDbDataReader areader = command.ExecuteReader();
            while (areader.Read())
            {
                Google.DealerPickUpRequest info = new Google.DealerPickUpRequest();
                if (areader["LATITUDE"].ToString() == "")
                {
                    Google.GeoCodeStruct geo = Google.GeoCodes.getGeoCode(areader["ADDR"].ToString() + " " + areader["City"].ToString() + " " + areader["State"].ToString() + " " + areader["ZipCode"].ToString());
                    geo.abcAddressID = Convert.ToInt32(areader["CODE"]);
                    Google.GeoCodes.addAddressGeoCode(geo);
                    info.Latitude = geo.Latitude;
                    info.Longitude = geo.Longitude;
                }
                else
                {
                    info.Latitude = areader["LATITUDE"].ToString();
                    info.Longitude = areader["LONGITUDE"].ToString();
                }

                info.Dealername = areader["DEALERCODE"].ToString();
                info.Address = areader["ADDR"].ToString();
                info.City = areader["City"].ToString();
                info.ZipCode = areader["ZipCode"].ToString();
                info.State = areader["State"].ToString();
                info.ContentDescription = areader["NOTE"].ToString();
                DateTime tripDate = Convert.ToDateTime(areader["TRIPDATE"]);
                info.tripDate = tripDate;
                info.tech = areader["TECH"].ToString();
                info.note = areader["NOTE"].ToString();
                #region dayOfWeek
                switch (tripDate.DayOfWeek)
                {
                    case DayOfWeek.Friday:
                        info.imagePath = "../icons/dealerFriday.png";
                        break;
                    case DayOfWeek.Monday:
                        info.imagePath = "../icons/dealerMonday.png";
                        break;
                    case DayOfWeek.Saturday:
                        info.imagePath = "../icons/dealerSaturday.png";
                        break;
                    case DayOfWeek.Sunday:
                        break;
                    case DayOfWeek.Thursday:
                        info.imagePath = "../icons/dealerThursday.png";
                        break;
                    case DayOfWeek.Tuesday:
                        info.imagePath = "../icons/dealerTuesday.png";
                        break;
                    case DayOfWeek.Wednesday:
                        info.imagePath = "../icons/dealerWednesday.png";
                        break;
                    default:
                        break;
                }
                #endregion

                requests.Add(info);
            }

            return requests;
        }
        public static Google.MapMarkerCollection<Google.currentTrips> getListOfTrips(DateTime startDate, 
                                                                                     DateTime endDate, 
                                                                                     String tech , 
                                                                                     List<string> listOfUnKnownGeoCodes)
        {
            
            DateTime endTime = new DateTime(endDate.Year, endDate.Month, endDate.Day,23, 59, 59);
            string newEndDate = String.Format("{0:MM/dd/yyyy HH:m:s}", endTime);

            string sqlString = @"SELECT tabTRIP.INVOICE, tabTRIP.TRIPDATE, tabTRIP.JOBSIZE, tabTRIP.TECH, tabWORKORDER.CATEGORY, tabWORKORDER.MODEL, tabADDRESS.LATITUDE, tabADDRESS.LONGITUDE, tabADDRESS.CODE AS CODE, tabADDRESS.ADDR, tabZIP.ZIPCODE, tabZIP.CITY,tabZIP.STATE
FROM tabZIP RIGHT JOIN ((tabWORKORDER RIGHT JOIN tabTRIP ON tabWORKORDER.INVOICE = tabTRIP.INVOICE) LEFT JOIN tabADDRESS ON tabWORKORDER.OWNERCODE = tabADDRESS.CODE) ON tabZIP.ZIPCODE = tabADDRESS.ZIP
            WHERE ( ((tabTRIP.INVOICE)>0) AND
            ( (tabADDRESS.CODE) is not null) AND 
            ( (tabTRIP.TRIPDATE) Between #" + String.Format("{0:MM/dd/yyyy}", startDate) + "# And #" + newEndDate + "#) ";
            //added new query because this new one will not include records having no USERCODE and INVOICES = 0. 
            if (tech != "*")
            {
                sqlString += "AND ((tabTRIP.TECH)='" + tech + "')) ORDER BY tabTRIP.TRIPDATE";
            }
            else
            {
                sqlString += ") ORDER BY tabTRIP.TRIPDATE";
            }
            OleDbCommand command = new OleDbCommand(sqlString, ABC.Database.DataBaseConnection.MyConn);
            OleDbDataReader aReader = command.ExecuteReader();
            //fill up a List of ABC.Google.currentTrips        
            Google.MapMarkerCollection<Google.currentTrips> currTripList = new Google.MapMarkerCollection<Google.currentTrips>();

            while (aReader.Read())
            {
                Google.currentTrips currTrip = new Google.currentTrips();
                currTrip.invoice = Convert.ToInt32(aReader["INVOICE"]);
                
                if (aReader["LATITUDE"].ToString() == "")
                {
                    Customer.Address custAddr = new Customer.Address(Convert.ToInt32(aReader["CODE"]));

                    Google.GeoCodeStruct ge = new Google.GeoCodeStruct();

                    for(int i=0;i<2;i++)
                    {
                        try
                        {
                            ge = Google.GeoCodes.getGeoCode(custAddr.CustAddress + " " + custAddr.City + " " + custAddr.State + " " + custAddr.ZipCode);
                            if (ge.Latitude != null)
                               break;
                        }
                        catch (GeoCodeLocationNotFoundException ex)
                        {
                            if (i == 1)
                                listOfUnKnownGeoCodes.Add(currTrip.invoice + " Exception Message: " + ex.Message);
                        }
                        catch (Exception ex)
                        {
                            if (i == 1)
                                listOfUnKnownGeoCodes.Add(currTrip.invoice + " Exception Message: " + ex.Message);
                        }
                       
                        if(i!=1)
                          Thread.Sleep(2000);

                    }           

                    ge.abcAddressID = Convert.ToInt32(aReader["CODE"]);
                    Google.GeoCodes.addAddressGeoCode(ge);
                    currTrip.Latitude = ge.Latitude;
                    currTrip.Longitude = ge.Longitude;
                }
                else
                {
                    currTrip.Latitude = aReader["LATITUDE"].ToString();
                    currTrip.Longitude = aReader["LONGITUDE"].ToString();
                }

                DateTime tripDate = Convert.ToDateTime(aReader["TRIPDATE"]);
                currTrip.dayOfWeek = tripDate.DayOfWeek;
                currTrip.tripDate = tripDate;
                currTrip.tech = aReader["TECH"].ToString();
                currTrip.category = aReader["CATEGORY"].ToString();
                currTrip.City = aReader["CITY"].ToString();
                currTrip.Zip = aReader["ZipCode"].ToString();
                currTrip.state = aReader["State"].ToString();
                currTrip.address = aReader["addr"].ToString();
                currTrip.jobSize = aReader["JobSize"].ToString();

                #region dayOfWeek
                switch (tripDate.DayOfWeek)
                {
                    case DayOfWeek.Friday:
                        currTrip.imagePath = "../icons/friday.png";
                        break;
                    case DayOfWeek.Monday:
                        currTrip.imagePath = "../icons/monday.png";
                        break;
                    case DayOfWeek.Saturday:
                        currTrip.imagePath = "../icons/saturday.png";
                        break;
                    case DayOfWeek.Sunday:
                        break;
                    case DayOfWeek.Thursday:
                        currTrip.imagePath = "../icons/thursday.png";
                        break;
                    case DayOfWeek.Tuesday:
                        currTrip.imagePath = "../icons/tuesday.png";
                        break;
                    case DayOfWeek.Wednesday:
                        currTrip.imagePath = "../icons/wednesday.png";
                        break;
                    default:
                        break;
                }
                #endregion
                currTripList.Add(currTrip);
            }
            aReader.Close();
            return currTripList;
        }
        //Function Purpose: Get summary of the job sizes

        public static List<JobSizeInfo> getJobSize(DateTime startDate,DateTime endDate, string tech)
        {
            //1/6/2013, 120
            //1/7/2014, 110
            List<JobSizeInfo> list = new List<JobSizeInfo>();
            DateTime endTime = new DateTime(endDate.Year, endDate.Month, endDate.Day, 23, 59, 59);
            string newEndDate = String.Format("{0:MM/dd/yyyy HH:m:s}", endTime);

//            string sqlString = @"SELECT sum(tabTRIP.JOBSIZE)  as sumOfJobSize, Format( tabTRIP.TRIPDATE, 'mm/dd/yyyy') as TRIPDATE FROM tabZIP RIGHT JOIN ((tabWORKORDER RIGHT JOIN tabTRIP ON tabWORKORDER.INVOICE = tabTRIP.INVOICE) LEFT JOIN tabADDRESS ON tabWORKORDER.OWNERCODE = tabADDRESS.CODE) ON tabZIP.ZIPCODE = tabADDRESS.ZIP
//            WHERE ( ((tabTRIP.INVOICE)>0) AND
//            ( (tabADDRESS.CODE) is not null) AND 
//            ( (tabTRIP.TRIPDATE) Between #" + String.Format("{0:MM/dd/yyyy}", startDate) + "# And #" + newEndDate + "#) ";

            //string sqlString = "SELECT Sum(tabTRIP.JOBSIZE) AS SumOfJOBSIZE1, Format([TRIPDATE],\"mm/dd/yyyy\") AS TRIPDATE1 ";
            //sqlString += "FROM tabZIP RIGHT JOIN ((tabWORKORDER RIGHT JOIN tabTRIP ON tabWORKORDER.INVOICE = tabTRIP.INVOICE) LEFT JOIN tabADDRESS ON tabWORKORDER.OWNERCODE = tabADDRESS.CODE) ON tabZIP.ZIPCODE = tabADDRESS.ZIP";
            //      sqlString += " WHERE ( ((tabTRIP.INVOICE)>0) AND ( (tabADDRESS.CODE) is not null) AND ";
            //sqlString+= "( (tabTRIP.TRIPDATE) Between #" + String.Format("{0:MM/dd/yyyy}", startDate) + "# And #" + newEndDate + "#) ";

            string sqlString = "SELECT Sum(tabTRIP.JOBSIZE) AS SumOfJOBSIZE1, Format([TRIPDATE],\"mm/dd/yyyy\") AS TRIPDATE1";
            sqlString +=" FROM tabZIP RIGHT JOIN ((tabWORKORDER RIGHT JOIN tabTRIP ON tabWORKORDER.INVOICE = tabTRIP.INVOICE) LEFT JOIN tabADDRESS ON tabWORKORDER.OWNERCODE = tabADDRESS.CODE) ON tabZIP.ZIPCODE = tabADDRESS.ZIP";
            sqlString += " WHERE ( ((tabTRIP.INVOICE)>0) AND ( (tabADDRESS.CODE) is not null) AND ( (tabTRIP.TRIPDATE) Between #" + String.Format("{0:MM/dd/yyyy}", startDate) + "# And #" + newEndDate + "#) ";

            if (tech != "*")
            {
                sqlString += "AND ((tabTRIP.TECH)='" + tech + "')) GROUP BY Format([TRIPDATE],\"mm/dd/yyyy\") ";
            }
            else
            {
                sqlString += ") GROUP BY Format([TRIPDATE],\"mm/dd/yyyy\"); ";
            }

            OleDbCommand command = new OleDbCommand(sqlString, ABC.Database.DataBaseConnection.MyConn);
            OleDbDataReader areader = command.ExecuteReader();
            while (areader.Read())
            {
                JobSizeInfo info = new JobSizeInfo();
                
                TimeSpan span = new TimeSpan(0, Convert.ToInt16(areader["sumOfJobSize1"]), 0);

                int normalizedHours = span.Hours;
                int normalizedMinutes = span.Minutes;


                info.jobSize = span.ToString();
                info.TripDate = string.Format("{0:MM/dd/yyyy}",areader["TRIPDATE1"]);
                list.Add(info);
            }
            areader.Close();
            
            return list;

        }

        public static DataTable getCalendarTrips()//DateTime start, DateTime end, string tech = "*")
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("start", typeof(DateTime));
            dt.Columns.Add("end", typeof(DateTime));
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("column1",typeof(string));

            dt.Columns.Add("id", typeof(string));
            DateTime start = new DateTime(2012, 1, 1);
            DateTime end = new DateTime(2014, 1, 1);
            string tech = "*";

            string sqlString2 = @"SELECT tabTRIP.INVOICE, tabTRIP.STARTWINDOWTIME ,tabTRIP.ENDWINDOWTIME , tabTRIP.JOBSIZE, tabTRIP.TRIPDATE, tabTRIP.JOBSIZE, tabTRIP.TECH, tabWORKORDER.CATEGORY, tabWORKORDER.MODEL, tabADDRESS.LATITUDE, tabADDRESS.LONGITUDE, tabADDRESS.CODE AS CODE, tabZIP.ZIPCODE, tabZIP.CITY
FROM tabZIP RIGHT JOIN ((tabWORKORDER RIGHT JOIN tabTRIP ON tabWORKORDER.INVOICE = tabTRIP.INVOICE) LEFT JOIN tabADDRESS ON tabWORKORDER.OWNERCODE = tabADDRESS.CODE) ON tabZIP.ZIPCODE = tabADDRESS.ZIP
            WHERE ( ((tabTRIP.INVOICE)>0) AND
            ( (tabADDRESS.CODE) is not null) AND 
            ( (tabTRIP.TRIPDATE) Between #" + String.Format("{0:MM/dd/yyyy}", start) + "# And #" + String.Format("{0:MM/dd/yyyy}", end) + "#) ";
            if (tech != "*")
            {
                sqlString2 += "AND ((tabTRIP.TECH)='" + tech + "'))";
            }
            else
            {
                sqlString2 += ")";
            }
            DataRow dr;
            {
                try
                {
                    
              
                    OleDbConnection con = ABC.Database.DataBaseConnection.MyConn;
                    OleDbCommand command= new OleDbCommand(sqlString2, con);

                    OleDbDataReader aReader = command.ExecuteReader();
                    while (aReader.Read())
                    {
                        dr = dt.NewRow();
                        DateTime tripDate = Convert.ToDateTime(aReader["TRIPDATE"]);

                        if (Convert.IsDBNull(aReader["ENDWINDOWTIME"]) || Convert.IsDBNull(aReader["STARTWINDOWTIME"]) ||
                            Convert.ToInt16(aReader["STARTWINDOWTIME"]) == 0 || Convert.ToInt16(aReader["ENDWINDOWTIME"]) == 0)
                        {
                            dr["start"] = tripDate;
                            int currJobSize = Convert.ToInt16(aReader["JOBSIZE"]);
                            if (currJobSize == 0)
                                dr["end"] = tripDate.AddMinutes(40).ToString();
                            else
                                dr["end"] = tripDate.AddMinutes(Convert.ToInt16(aReader["JOBSIZE"]));
                        }
                        else
                        {
                           
                            int startit = Convert.ToInt16(aReader["STARTWINDOWTIME"]);
                            int endVar;
                            if (Convert.ToInt16(aReader["ENDWINDOWTIME"]) >= 1 && Convert.ToInt16(aReader["ENDWINDOWTIME"]) <= 5)
                                endVar = Convert.ToInt16(aReader["ENDWINDOWTIME"]) + 12;
                            else
                                endVar = Convert.ToInt16(aReader["ENDWINDOWTIME"]);

                            DateTime startWindow = new DateTime(tripDate.Year, tripDate.Month, tripDate.Day, startit, 0, 0);
                            DateTime endWindow = new DateTime(tripDate.Year, tripDate.Month, tripDate.Day, endVar, 0, 0);
                            dr["start"] = startWindow;
                            dr["end"] = endWindow;
                        }
                        dr["id"] = aReader["INVOICE"];
                        dr["name"] = "Invoice " + aReader["INVOICE"] + " JobSize:" + aReader["JOBSIZE"] + " " + aReader["TECH"] + " " + aReader["CITY"];
                        dr["column1"] = aReader["TECH"].ToString();
                        dt.Rows.Add(dr);
                    }
                }
                catch (Exception )
                {
                    throw;
                }
                finally
                {

                }

                return dt;
            }
          
        }
        public static DataTable getRecords(DateTime start, int days, String tech = null)
        {
            DataTable dt;
            dt = new DataTable();
            dt.Columns.Add("start", typeof(DateTime));
            dt.Columns.Add("end", typeof(DateTime));
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("id", typeof(string));
            string sqlString2 = @"SELECT tabTRIP.INVOICE, tabTRIP.STARTWINDOWTIME ,tabTRIP.ENDWINDOWTIME , tabTRIP.JOBSIZE, tabTRIP.TRIPDATE, tabTRIP.JOBSIZE, tabTRIP.TECH, tabWORKORDER.CATEGORY, tabWORKORDER.MODEL, tabADDRESS.LATITUDE, tabADDRESS.LONGITUDE, tabADDRESS.CODE AS CODE, tabZIP.ZIPCODE, tabZIP.CITY
FROM tabZIP RIGHT JOIN ((tabWORKORDER RIGHT JOIN tabTRIP ON tabWORKORDER.INVOICE = tabTRIP.INVOICE) LEFT JOIN tabADDRESS ON tabWORKORDER.OWNERCODE = tabADDRESS.CODE) ON tabZIP.ZIPCODE = tabADDRESS.ZIP
            WHERE ( ((tabTRIP.INVOICE)>0) AND
            ( (tabADDRESS.CODE) is not null) AND 
            ( (tabTRIP.TRIPDATE) Between #" + String.Format("{0:MM/dd/yyyy}", start) + "# And #" + start.AddDays(days) + "#) ";
            if (tech != "*")
            {
                sqlString2 += "AND ((tabTRIP.TECH)='" + tech + "'))";
            }
            else
            {
                sqlString2 += ")";
            }
            DataRow dr;
            {
                try
                {
           
                    OleDbCommand command = new OleDbCommand(sqlString2, ABC.Database.DataBaseConnection.MyConn);          
                    OleDbDataReader aReader = command.ExecuteReader();
                    while (aReader.Read())
                    {
                        dr = dt.NewRow();
                        DateTime tripDate = Convert.ToDateTime(aReader["TRIPDATE"]);

                        if ( Convert.IsDBNull(aReader["ENDWINDOWTIME"]) || Convert.IsDBNull(aReader["STARTWINDOWTIME"]) ||
                            Convert.ToInt16(aReader["STARTWINDOWTIME"]) == 0 || Convert.ToInt16(aReader["ENDWINDOWTIME"]) == 0)
                        {
                            dr["start"] = tripDate;
                            int currJobSize = Convert.ToInt16(aReader["JOBSIZE"]);
                            if (currJobSize == 0 )
                                dr["end"] = tripDate.AddMinutes(40).ToString();
                            else
                                dr["end"] = tripDate.AddMinutes(Convert.ToInt16(aReader["JOBSIZE"]));
                        }
                        else
                        {
                            //DateTime endTime = new DateTime(endDate.Year, endDate.Month, endDate.Day, 12, 59, 59);
                            //string newEndDate = String.Format("{0:MM/dd/yyyy h:m:s}", endTime);
                            int startit = Convert.ToInt16(aReader["STARTWINDOWTIME"]);
                            int end;
                            if (Convert.ToInt16(aReader["ENDWINDOWTIME"]) >= 1 && Convert.ToInt16(aReader["ENDWINDOWTIME"]) <= 5)
                                end = Convert.ToInt16(aReader["ENDWINDOWTIME"]) + 12;
                            else
                                end = Convert.ToInt16(aReader["ENDWINDOWTIME"]);

                            DateTime startWindow = new DateTime(tripDate.Year, tripDate.Month, tripDate.Day, startit, 0, 0);
                            DateTime endWindow = new DateTime(tripDate.Year, tripDate.Month, tripDate.Day, end, 0, 0);
                            dr["start"] = startWindow;
                            dr["end"] = endWindow;
                        }
                        dr["id"] = aReader["INVOICE"];
                        dr["name"] = "Invoice " + aReader["INVOICE"] + " JobSize:" + aReader["JOBSIZE"] + " " + aReader["TECH"] + " " + aReader["CITY"];
                        dt.Rows.Add(dr);
                    }
                }
                catch (Exception )
                {
                    throw;
                }
                finally
                {

                }

                return dt;
            }
        }

        public struct JobSizeInfo
        {
            public string TripDate;
            public string jobSize;
        }
    }
}
